/**
 * 需要用户登录的路由
 */

import { Router } from "express";
const router = Router();

import db from "../database.js";
import { v4 as uuid } from "uuid";
import sendMail from "../mail.js";
import dayjs from "dayjs";
// 导入登录验证和参数验证中间件
import { auth, upload, validate } from "../middlewares.js";
// 导入参数验证器
import validators from "../validate.js";
import { isEmpty, orderBy, writeFile, deleteFile } from "../utils.js";
import path from "node:path";

const { EMAIL, DOMAIN } = process.env;
async function sendActiveEmail(user) {
  // 发送激活邮件
  const info = await sendMail({
    from: `部落格的管理员 <${EMAIL}>`,
    to: user.email,
    subject: "激活账号",
    html: `<h1>Blog 账号激活</h>
			<p>欢迎加入到我的博客世界，
			请点击<a href="${DOMAIN}/user/active?code=${user.password}">链接</a>激活你的账号。
			</p>`,
    text: ``,
  });
  return info;
}
// router 全局验证登录状态
router.use(auth);

router.post("/user/invite", validate(validators.email), async (req, res) => {
  const { email } = req.body;
  const exists = await db.findOne`SELECT * FROM User WHERE email = ${email}`;
  if (exists) {
    // 如果账户已经激活
    if (exists.active) {
      return res.send({
        success: false,
        message: "账户已经激活了！",
      });
    }
    // 邀请邮件最多每小时发一次
    const now = dayjs(),
      updatedAt = dayjs(exists.updatedAt);
    if (now.diff(updatedAt, "hour") > 1) {
      await db.run`UPDATE User SET updatedAt = now() WHERE id = ${exists.id}`;
      // 重发邀请邮件
      sendActiveEmail(exists);
      return res.send({
        success: true,
        message: "已经重发邀请邮件！",
      });
    } else {
      return res.send({
        success: false,
        message: "邀请邮件已经发送过了！",
      });
    }
  }
  // 保存用户信息
  const id = await db.insert`INSERT INTO User (email, password, nickname)
			VALUES (${email}, ${uuid().replaceAll("-", "")}, 'unknown')`;
  const user = await db.findOne`SELECT * FROM User WHERE id = ${id}`;
  // 发送邮件
  sendActiveEmail(user);
  res.send({
    success: true,
    message: "用户邀请已经发出！",
  });
});

// 新建博客
router.post(
  "/user/posts",
  upload.single("cover"),
  validate(
    validators.title,
    validators.digest,
    validators.tags,
    validators.published
  ),
  async (req, res) => {
    const { title, digest, content, published, tags } = req.body;
    // 保存封面
    const filename = uuid() + path.extname(req.file.originalname);
    await writeFile(`./public/cover/${filename}`, req.file.buffer);
    // 保存博客
    const userId = req.session.userId;
    const id =
      await db.insert`insert into Post (title, cover, digest, content, published, authorId)
            values(${title}, ${`/cover/${filename}`}, ${digest}, ${content}, ${published}, ${userId})`;
    const post = await db.findOne`select * from Post where id = ${id}`;
    // 将 tags 存入 PostTags 表中
    if (tags?.length) {
      await db.run`INSERT INTO PostTags (postId, tagId) 
      VALUES ${tags.map((i) => [post.id, i])}`;
    }
    res.send({
      success: true,
      message: "博客创建成功!",
      object: post,
    });
  }
);

async function getPostTags(postId) {
  const tags =
    await db.findAll`select tagId from PostTags where postId = ${postId}`;
  return tags.reduce((p, c) => (p.push(c.tagId), p), []);
}

async function saveCover(req) {
  if (req.file) {
    const filename = uuid() + path.extname(req.file.originalname);
    await writeFile(`./public/cover/${filename}`, req.file.buffer);
    return `/cover/${filename}`;
  }
  return req.body.cover;
}

// 修改博客
router.put(
  "/user/posts/:id",
  upload.single("cover"),
  validate(
    validators.title,
    validators.digest,
    validators.published,
    validators.tags
  ),
  async (req, res) => {
    const { title, digest, content, published, tags } = req.body;
    const { id } = req.params;
    // 判定该博客是否存在
    const exists = await db.findOne`select * from Post where id = ${id}`;
    if (!exists) {
      return res.send({
        success: false,
        message: "该博客不存在!",
      });
    }
    const userId = req.session.userId;
    // 有两种情况：有图片上传更新或无图片上传更新
    const cover = await saveCover(req);
    await db.run`UPDATE Post SET title=${title}, cover=${cover}, digest=${digest},
          content=${content}, published=${published}, updatedAt=now() 
        WHERE id=${id} and authorId=${userId}`;
    // 更新 PostTags 表
    if (tags?.length) {
      // 获取原来的 tags
      const oldTags = await getPostTags(id);
      const deleteIds = oldTags.filter((tag) => !tags.includes(tag));
      if (deleteIds.length)
        await db.run`DELETE FROM PostTags WHERE postId = ${id} and tagId in (${deleteIds})`;
      const insertIds = tags.filter((tag) => !oldTags.includes(tag));
      if (insertIds.length)
        await db.run`INSERT INTO PostTags (postId, tagId)
          VALUES ${insertIds.map((i) => [id, i])}`;
    }
    res.send({
      success: true,
      message: "博客修改成功!",
      object: await getPostById(id, userId),
    });
  }
);

async function getPostById(id, authorId) {
  const post =
    await db.findOne`select * from Post where id = ${id} and authorId = ${authorId}`;
  post.published = post.published === 1;
  // 获取关联的标签
  const tags =
    await db.findAll`select tagId from PostTags where postId = ${id}`;
  post.tags = tags.reduce((p, c) => (p.push(c.tagId), p), []);
  return post;
}

// 获取单个博客
router.get("/user/posts/:id", async (req, res) => {
  const { id } = req.params,
    { userId } = req.session;

  res.send({
    success: true,
    object: await getPostById(id, userId),
  });
});

// 查询用户博客
router.get(
  "/user/posts",
  validate(
    validators.query.title,
    validators.query.start_date,
    validators.query.end_date,
    validators.query.tags,
    validators.query.orderby(["hits", "createdAt"]),
    validators.query.limit,
    validators.query.offset
  ),
  async (req, res) => {
    const {
      title,
      start_date,
      end_date,
      tags,
      orderby = "-createdAt",
      limit = 5,
      offset = 0,
    } = req.query;
    const userId = req.session.userId;
    const where = ["authorId = ?"],
      params = [userId];
    if (title) {
      // 忽略大小写
      where.push("LOWER(title) like LOWER(?)");
      params.push(`%${title}%`);
    }
    if (start_date) {
      where.push(`createdAt >= ?`);
      params.push(start_date);
    }
    if (end_date) {
      where.push(`createdAt <= ?`);
      params.push(`${end_date} 23:59:59`);
    }
    //
    let table = "Post as a";
    // 多表连接
    if (Array.isArray(tags) && tags.length > 0) {
      table = "Post as a inner join PostTags as b on a.id = b.postId";
      where.push("b.tagId in (?)");
      params.push(tags);
    }
    // where 子句
    const whereSql = isEmpty(where) ? "" : `where ${where.join(" and ")}`;
    // 计算总数
    const { count } = await db.findOne(
      `select count(distinct a.id) as count from ${table} ${whereSql}`,
      params
    );
    // 排序
    const orderbySql = orderBy(orderby) ? `order by ${orderBy(orderby)}` : "";
    // 分页查询
    const sql = `select distinct a.* from ${table} ${whereSql} ${orderbySql} limit ${limit} offset ${offset};`;
    const posts = await db.findAll(sql, params);
    res.send({
      success: true,
      query: {
        count,
        limit,
        offset,
        page: offset / limit + 1,
        results: posts,
      },
    });
  }
);

// 新建评论
router.post(
  "/user/comments",
  validate(validators.postId, validators.description),
  async (req, res) => {
    const { postId, description } = req.body;
    const userId = req.session.userId;
    // 判断该帖子是否存在
    const exists = await db.findOne`select * from Post where id = ${postId}`;
    if (!exists) {
      return res.send({
        success: false,
        message: "该帖子不存在!",
      });
    }
    const id =
      await db.insert`insert into Comment (postId, description, authorId) values(${postId}, ${description}, ${userId})`;
    const comment =
      await db.findOne`select a.*, b.nickname, b.avatar from Comment a JOIN User b on a.authorId = b.id where a.id = ${id}`;
    res.send({
      success: true,
      message: "评论创建成功!",
      object: comment,
    });
  }
);

// 删除评论
router.delete("/user/comments/:id", async (req, res) => {
  const { id } = req.params;
  const userId = req.session.userId;
  // 判断该评论是否存在
  const exists = await db.findOne`select * from Comment where id = ${id}`;
  if (!exists) {
    return res.send({
      success: false,
      message: "该评论不存在!",
    });
  }
  const { postId, authorId } =
    await db.findOne`select * from Comment where id = ${id}`;
  if (userId == postId || userId == authorId) {
    await db.run`delete from Comment where id=${id}`;
    return res.send({
      success: true,
      message: "评论删除成功!",
    });
  }
  res.send({
    success: false,
    message: "权限不足!",
  });
});

// 新建用户标签
router.post("/user/tags", validate(validators.name), async (req, res) => {
  const { name } = req.body;
  const userId = req.session.userId;
  const exists =
    await db.findOne`select * from Tag where name = ${name} and ownerId=${userId}`;
  // 判断 Tags 中是否已经存在 name
  if (exists) {
    return res.send({
      success: false,
      message: "标签已经存在了!",
    });
  }
  const id =
    await db.insert`insert into Tag (name,createdAt,ownerId) values(${name}, now(), ${userId})`;
  const tag = await db.findOne`select * from Tag where id = ${id}`;
  res.send({
    success: true,
    message: "标签创建成功!",
    object: tag,
  });
});

// 修改标签
router.put("/user/tags/:id", validate(validators.name), async (req, res) => {
  const { name } = req.body;
  const { id } = req.params;
  const userId = req.session.userId;
  // 判断是否存在该标签
  const tagIdExists =
    await db.findOne`select * from Tag where id = ${id} and ownerId=${userId}`;
  if (!tagIdExists) {
    return res.send({
      success: false,
      message: "该标签不存在!",
    });
  }
  // 判断 Tags 中是否已经存在 name
  const nameExists =
    await db.findOne`select * from Tag where name = ${name} and ownerId=${userId}`;
  if (nameExists) {
    return res.send({
      success: false,
      message: "该标签名已经存在了!",
    });
  }
  await db.run`update Tag set name=${name} where id=${id}`;
  const tag = await db.findOne`select * from Tag where id = ${id}`;
  res.send({
    success: true,
    message: "标签修改成功!",
    object: tag,
  });
});

// 删除标签
router.delete("/user/tags/:id", async (req, res) => {
  const { id } = req.params;
  const userId = req.session.userId;
  // 判断是否存在该标签
  const tagIdExists =
    await db.findOne`select * from Tag where id = ${id} and ownerId = ${userId}`;
  if (!tagIdExists) {
    return res.send({
      success: false,
      message: "该标签不存在!",
    });
  }
  await db.run`delete from PostTags where tagId = ${id}`;
  await db.run`delete from Tag where id=${id}`;
  res.send({
    success: true,
    message: "标签删除成功!",
  });
});

// 获取用户标签
router.get("/user/tags", async (req, res) => {
  const userId = req.session.userId;
  const tags =
    await db.findAll`select * from Tag where ownerId = ${userId} or ownerId is null`;
  res.send({
    success: true,
    query: {
      results: tags,
    },
  });
});

// 保存简历 (如果存在就是修改简历，如果不存在就是新建简历)
router.post(
  "/user/resume",
  upload.single("photo"),
  validate(
    validators.name,
    validators.job,
    validators.basic,
    validators.contact,
    validators.education,
    validators.skills,
    validators.workExperience
  ),
  async (req, res) => {
    // 从 session 中获取用户 id
    const id = req.session.userId;
    const { name, job, basic, contact, education, skills, workExperience } =
      req.body;

    // // 判断当前用户是否拥有简历
    const exists =
      await db.findOne`select * from Resume where authorId = ${id}`;

    // 判断 photo 是不是 file
    let photo = req.body.photo;
    if (req.file) {
      // 如果是，说明上传了新头像，需要把旧头像删除
      const filename = uuid() + path.extname(req.file.originalname);
      await writeFile(`./public/photo/${filename}`, req.file.buffer);
      photo = `/photo/${filename}`;
      // 删除文件中的 oldPhoto
      if (exists) await deleteFile(`./public${exists.photo}`);
    }

    if (exists) {
      // 更新数据库
      await db.run`update Resume set name=${name}, basic=${basic}, job=${job}, photo=${photo}, contact=${contact}, education=${education}, skills=${skills}, workExperience=${workExperience},updatedAt=now() where authorId = ${id}`;
    } else {
      // 如果不存在，插入数据
      await db.insert`insert into Resume (name, job, basic, photo, contact, education, skills, workExperience,createdAt, authorId) values(${name}, ${job}, ${basic}, ${photo}, ${contact}, ${education}, ${skills}, ${workExperience},NOW(), ${id})`;
    }
    // 完成后查询结果
    const resume =
      await db.findOne`select * from Resume where authorId = ${id}`;
    res.send({
      success: true,
      message: "简历保存成功!",
      object: resume,
    });
  }
);

// 获取用户简历
router.get("/user/resume", async (req, res) => {
  // 先判断用户是否拥有简历
  const id = req.session.userId;
  const result = await db.findOne`select * from Resume where authorId = ${id}`;
  if (result) {
    return res.send({
      success: true,
      object: result,
    });
  }
  return res.send({
    success: false,
    message: "还没有简历信息！赶紧去添加吧。",
  });
});

export default (app) => {
  app.use("/api", router);
};
